package work.vcloud.baseinfo.utils;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ImportExcelUtil {



	public List<Map<String, Object>> readExcel(InputStream in, List<Map<String, Object>> colList) {
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		try {
			// poi读取excel
			// 创建要读入的文件的输入流
			InputStream inp = in;
			// 根据上述创建的输入流 创建工作簿对象
			Workbook wb = WorkbookFactory.create(inp);
			// 得到第一页 sheet
			// 页Sheet是从0开始索引的
			Sheet sheet = wb.getSheetAt(0);
			int rowNum = sheet.getPhysicalNumberOfRows();

			List<String> headList = new ArrayList<String>();
			Row row0 = sheet.getRow(0);
			int cols = row0.getPhysicalNumberOfCells();
			for (int i = 0; i < cols; i++) {
				Cell cell = row0.getCell(i);
				String head = getCellFormatValue(cell);
				for(int j=0;j<colList.size();j++) {
					Map<String, Object> col = colList.get(j);
					if(head.equals(col.get("name"))){
						headList.add(col.get("col").toString());
					}
				}
			}
			for (int i = 1; i < rowNum; i++) {
				Row row = sheet.getRow(i);
				Map<String, Object> dataMap = new HashMap<String, Object>();
				for (int j = 0; j < headList.size(); j++) {
					Cell cell = row.getCell(j);
					dataMap.put(headList.get(j), getCellFormatValue(cell));
				}
				dataList.add(dataMap);
			}
			// 关闭输入流
			inp.close();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dataList;
	}

	
	
	private static String getCellFormatValue(Cell cell) {
		String cellvalue = null;
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				cellvalue = String.valueOf(cell.getNumericCellValue());
				if(cellvalue.lastIndexOf(".0") != -1) {
					cellvalue = cellvalue.substring(0, cellvalue.lastIndexOf(".0"));
				}
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
					Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
					cellvalue = sdf.format(date).toString();
				}
				//  判断是否为科学计数法（包含E、e、+等符号）
				if ((cellvalue).indexOf("E")!=-1 || (cellvalue).indexOf("e")!=-1 || (cellvalue).indexOf("+")!=-1) {
					BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
					cellvalue = bd.toString();
				}
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				cellvalue = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				cellvalue = null;
				break;
			case HSSFCell.CELL_TYPE_FORMULA: {
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
					cellvalue = sdf.format(date);
				}
				else {
					cellvalue = String.valueOf(cell.getNumericCellValue());
				}
				//  判断是否为科学计数法（包含E、e、+等符号）
				if ((cellvalue).indexOf("E")!=-1 || (cellvalue).indexOf("e")!=-1 || (cellvalue).indexOf("+")!=-1) {
					BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
					cellvalue = bd.toString();
				}
				break;
			}
			case HSSFCell.CELL_TYPE_STRING:
				cellvalue = cell.getRichStringCellValue().getString();
 				break;
			default:
				cellvalue = null;
			}
		} else {
			cellvalue = null;
		}
		
		if(cellvalue != null) {
			if(cellvalue.equals("")) {
				cellvalue = null;
			}
		}
		return cellvalue;
	}
 
	/**
     * 根据指定坐标判断元素是否相同，然后去重
     * 如果指定 的下标超出了list的长度范围，则不对该下标对应的数据进行查重判断，默认 是重复的
     * */
    public List<Map<String,String>>  removeDuplicatesListMapString(List<Map<String,String>> dataList, String[] validateDuplicateFieldsKey){
        if(dataList == null || dataList.size() == 0){
            return new ArrayList<Map<String,String>>();
        }
        if(validateDuplicateFieldsKey == null || validateDuplicateFieldsKey.length == 0){
            //就去效验所有的字段。
        }
        for(int i = 0 ; i < dataList.size(); i++){
            for(int j = i+1; j < dataList.size(); j++){
                if(j >= dataList.size()){
                    break;
                }
                //如果duplicatesMark的值和 validateDuplicateFieldsIndex数据额长度一样，代表数据相同
                int duplicatesMark = 0;
                for(int x = 0; x < validateDuplicateFieldsKey.length; x++){
                    String index = validateDuplicateFieldsKey[x];
                    if(index == null  || index.equals("")){
                        duplicatesMark+=1;
                        continue;
                    }
                    if(dataList.get(i).get(index).equals(dataList.get(j).get(index))){
                        duplicatesMark+=1;
                    }
                }
                if(duplicatesMark == validateDuplicateFieldsKey.length){
                    dataList.remove(j);
                    j-=1;
                }
            }
        }
        return dataList;
    }
	
	
}